package com.plm.mongo.crud;

import com.mongodb.Block;
import com.mongodb.MongoClient;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;
import com.mongodb.client.model.Filters;
import com.plm.mongo.util.DBUtil;
import com.plm.mongo.util.MongoUtil;
import org.bson.Document;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Duration;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author Limi
 * @create 2019-01-01 0:17
 */
public class TestMySQLMongo {

    public static MongoClient mongoClient = null;
    public static Connection connection = null;

    @BeforeClass
    public static void beforeClass(){
        mongoClient = MongoUtil.getMongoClient();
        connection = DBUtil.getConnection();
    }

    @AfterClass
    public static void afterClass(){
        MongoUtil.closeMongoClient(mongoClient);
        DBUtil.closeConnection(connection);
    }

    public MongoCollection getMongoCollection(){
        //获取数据库
        MongoDatabase db = mongoClient.getDatabase("test");
        //获取集合
        return db.getCollection("test");
    }

    //测试MySQL插入10万条数据
    @Test
    public void testMysqlInsert() throws SQLException {
        LocalDateTime stime = LocalDateTime.now();
        connection.setAutoCommit(false);
        PreparedStatement ps = connection.prepareStatement("insert into test (num) values(?)");
        for (int i=1;i<=100000;i++) {
            ps.setInt(1, i);
            ps.addBatch();
            if (i % 10000 == 0) {
                ps.executeBatch();
                connection.commit();
                ps.clearBatch();
            }
        }
        LocalDateTime etime = LocalDateTime.now();
        Duration duration = Duration.between(stime, etime);
        System.out.println("mysql插入10万条数据用时：" + duration.toMillis() +"毫秒");
    }

    //测试mongodb插入10万条数据
    @Test
    public void testMongoInsert(){
        MongoCollection collection = getMongoCollection();
        LocalDateTime stime = LocalDateTime.now();
        List<Document> list = new ArrayList<>();
        for (int i=1;i<=100000;i++) {
            list.add(Document.parse("{num:"+i+"}"));
            if (i % 10000 == 0) {
                collection.insertMany(list);
                list.clear();
            }
        }
        LocalDateTime etime = LocalDateTime.now();
        Duration duration = Duration.between(stime, etime);
        System.out.println("mongodb插入10万条数据用时：" + duration.toMillis() +"毫秒");
    }

    //测试mysql查询10万条数据
    @Test
    public void testMysqlQuery() throws SQLException {
        PreparedStatement ps = connection.prepareStatement("select * from test where num > ? and num < 100000");
        Random random = new Random();
        List<Long> times = new ArrayList<>(100);
        for (int i=1;i<=100;i++) {
            LocalDateTime stime = LocalDateTime.now();

            ps.setInt(1, random.nextInt(100000));
            ResultSet results = ps.executeQuery();
            while (results.next()){
                int id = results.getInt("_id");
                int num = results.getInt("num");
                System.out.println("id:"+id +", num:" + num);
            }

            LocalDateTime etime = LocalDateTime.now();
            Duration duration = Duration.between(stime, etime);
            times.add(duration.toMillis());
        }

        LongSummaryStatistics collect = times.stream().collect(Collectors.summarizingLong(time -> time));
        long max = collect.getMax();
        long min = collect.getMin();
        double average = collect.getAverage();
        long sum = collect.getSum();

        System.out.println("===================mysql 100次随机条件查询===================");
        System.out.println("最大耗时：" + max);
        System.out.println("最小耗时：" + min);
        System.out.println("平均耗时：" + average);
        System.out.println("总耗时：" + sum);
    }

    //测试mongodb查询10万条数据
    @Test
    public void testMongoQuery(){
        MongoCollection collection = getMongoCollection();
        Random random = new Random();
        List<Long> times = new ArrayList<>(100);
        for (int i=1;i<100;i++) {
            LocalDateTime stime = LocalDateTime.now();

            int randonInt = random.nextInt(100000);
            FindIterable results = collection.find(Filters.and(
                    Filters.gt("num", randonInt),
                    Filters.lt("num", 100000)
            ));
            results.forEach((Block<Document>) document -> System.out.println(document.toJson()));

            LocalDateTime etime = LocalDateTime.now();
            Duration duration = Duration.between(stime, etime);
            times.add(duration.toMillis());
        }
//        MongoCursor cursor = results.iterator();
//        while (cursor.hasNext()) {
//            System.out.println(((Document)cursor.next()).toJson());
//        }
//        cursor.close();

        LongSummaryStatistics collect = times.stream().collect(Collectors.summarizingLong(time -> time));
        long max = collect.getMax();
        long min = collect.getMin();
        double average = collect.getAverage();
        long sum = collect.getSum();

        System.out.println("===================mongodb 100次随机条件查询===================");
        System.out.println("最大耗时：" + max);
        System.out.println("最小耗时：" + min);
        System.out.println("平均耗时：" + average);
        System.out.println("总耗时：" + sum);
    }

}
